package com.kaifamiao.demo.d0905;

import java.sql.*;

public class Transfer {


    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            // 加载驱动类，注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/bank";
            String user = "root";
            String password = "";

            // 创建数据库连接
            connection = DriverManager.getConnection(url, user, password);
            // 开启事务, 不自动提交
            connection.setAutoCommit(false);

            // 创建 prepareStatement 对象, 参数使用 ? 占位
            String sql = "update customer set balance = balance + ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);

            // 设置参数， 替换 sql 中的 ?. 类型转换
            double money = 100;
            int source = 2, target = 1;

            preparedStatement.setObject(1, -money);
            preparedStatement.setObject(2, source);

            int row = preparedStatement.executeUpdate();

//            int i = 1 / 0;
            // 执行 SQL, 新兵钱 + 100
            preparedStatement.setObject(1, money);
            preparedStatement.setObject(2, target);

            row = preparedStatement.executeUpdate();

            // 提交事务
            connection.commit();
        } catch (Exception e) {
            e.printStackTrace();
            try {
                // 回滚事务
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        } finally {
            try {
                if (preparedStatement != null){
                    preparedStatement.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }
}
